﻿Imports System.Data.SqlServerCe
Imports Framework.Data

Public Class SharedItemsEx
    Shared Sub New()
        Dim aSelectColumns As String() = {"Id", "Name", "UniqueId", "Added", "ShareId" _
                                        , "LastChanged", "Directory", "TotalFiles", "TotalSize" _
                                        , "GroupMode", "ItemType", "HasMediaInfo", "Comment" _
                                        , "RelativeDirectory"}
        ObjectPersister.DefineObject(GetType(Entities.SharedItem), "SharedItems", aSelectColumns)

    End Sub
    Private Sub New()
    End Sub

    Public Shared Function GetAllByShareId(ByVal shareId As Integer, ByVal transactionScope As TransactionScope) As Generic.List(Of Entities.SharedItem)
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Return ObjectPersister.GetAll(Of Entities.SharedItem)("ShareId=@ShareId", transactionScope, sqlPar1)
    End Function
    Public Shared Function GetAllByShareIdAndAddedAfter(ByVal shareId As Integer, ByVal addedAfter As Date) As Generic.List(Of Entities.SharedItem)
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Dim sqlPar2 As New SqlCeParameter("@AddedAfter", addedAfter)
        Return ObjectPersister.GetAll(Of Entities.SharedItem)("ShareId=@ShareId AND Added>@AddedAfter", sqlPar1, sqlPar2)
    End Function

    Public Shared Function GetAllSubdirectoriesByShareIdAndRelativeDirectory(ByVal shareId As Integer, ByVal relativeDirectory As String) As Generic.List(Of String)
        Dim strSql As String = "select distinct relativedirectory from shareditems where ShareId=@ShareId AND RelativeDirectory<>@Dir1 AND RelativeDirectory LIKE @Dir2"
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Dim sqlPar2 As New SqlCeParameter("@Dir1", relativeDirectory)
        Dim sqlPar3 As New SqlCeParameter("@Dir2", relativeDirectory & "%")
        Return Environment.SqlToStringList(strSql, sqlPar1, sqlPar2, sqlPar3)
    End Function

    Public Shared Function GetAllSubdirectoriesByShareIdAndRelativeDirectoryAsInfos(ByVal shareId As Integer, ByVal relativeDirectory As String) As Generic.List(Of Entities.Infos.DirectoryInfo)
        Dim strSql As String = "select RelativeDirectory as Name,count(*) as TotalItems ,sum(totalsize) as TotalSize " & _
                               "from SharedItems " & _
                               "where ShareId = @ShareId " & _
                               "AND RelativeDirectory<>@Dir1 " & _
                               "AND RelativeDirectory LIKE @Dir2 " & _
                               "group by relativedirectory"
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Dim sqlPar2 As New SqlCeParameter("@Dir1", relativeDirectory)
        Dim sqlPar3 As New SqlCeParameter("@Dir2", relativeDirectory & "%")
        Return Environment.SqlToInfoObjects(Of Entities.Infos.DirectoryInfo)(strSql, sqlPar1, sqlPar2, sqlPar3)

    End Function

    Public Shared Function GetAllByShareIdAndRelativeDirectory(ByVal shareId As Integer, ByVal relativeDirectory As String) As Generic.List(Of Entities.SharedItem)
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Dim sqlPar2 As New SqlCeParameter("@Dir", relativeDirectory)
        Return ObjectPersister.GetAll(Of Entities.SharedItem)("ShareId=@ShareId AND RelativeDirectory=@Dir", sqlPar1, sqlPar2)
    End Function
    Public Shared Function GetAllByShareIdAndDirectoryRecursive(ByVal shareId As Integer, ByVal rootPath As String) As Generic.List(Of Entities.SharedItem)
        Dim sqlPar1 As New SqlCeParameter("@ShareId", shareId)
        Dim sqlPar2 As New SqlCeParameter("@Dir1", rootPath)
        Dim sqlPar3 As New SqlCeParameter("@Dir2", rootPath & "\%")
        Return ObjectPersister.GetAll(Of Entities.SharedItem)("ShareId=@ShareId AND (Directory=@Dir1 OR Directory LIKE @Dir2)", sqlPar1, sqlPar2, sqlPar3)
    End Function
    'Public Shared Function GetAllByDirectory(ByVal directory As String) As Generic.List(Of Entities.SharedItem)
    '    Return ObjectPersister.GetAll(Of Entities.SharedItem)(String.Format("Directory='{0}'", directory))
    'End Function
    Public Shared Function GetById(ByVal id As Integer, ByVal transactionScope As TransactionScope) As Entities.SharedItem
        Dim sqlPar1 As New SqlCeParameter("@Id", id)
        Return ObjectPersister.GetOne(Of Entities.SharedItem)("Id=@Id", transactionScope, sqlPar1)
    End Function

    Public Shared Function GetAllByItemTypeAndMissingGenres(ByVal itemType As Entities.ItemType) As Generic.List(Of Entities.SharedItem)
        Dim sqlPar1 As New SqlCeParameter("@ItemType", itemType)
        Dim strClause As String = "ItemType=@ItemType AND (HasMediaInfo=0 OR HasMediaInfo IS NULL OR Id IN (SELECT SharedItemId FROM MediaInfo WHERE Genres IS NULL))"
        Return ObjectPersister.GetAll(Of Entities.SharedItem)(strClause, sqlPar1)
    End Function
    Public Shared Function GetAllByItemTypeAndMissingFeatureNeedingAction(ByVal itemType As Entities.ItemType) As Generic.List(Of Entities.SharedItem)
        Dim sqlPar1 As New SqlCeParameter("@ItemType", itemType)
        Dim strClause As String = "ItemType=@ItemType AND (HasMediaInfo=0 OR HasMediaInfo IS NULL OR Id IN (SELECT SharedItemId FROM MediaInfo WHERE FeatureInfoStatus=0))"
        Return ObjectPersister.GetAll(Of Entities.SharedItem)(strClause, sqlPar1)
    End Function
    Public Shared Function GetAllByIds(ByVal ids As Generic.List(Of Integer)) As Generic.List(Of Entities.SharedItem)
        Dim strClause As String
        strClause = String.Format("Id IN ({0})", Framework.Serialization.ToCommaSepString(ids))
        Return ObjectPersister.GetAll(Of Entities.SharedItem)(strClause)
    End Function


    Public Shared Sub Delete(ByVal id As Integer, ByVal transactionScope As TransactionScope)
        Dim strSql As String = String.Format("DELETE FROM SharedItems WHERE Id={0}", id)
        Environment.ExecuteNonQuery(strSql, transactionScope)
    End Sub

    Public Shared Sub RemoveOrphans()
        Dim strSql As String = "DELETE FROM SharedItems WHERE ShareId NOT IN (SELECT Id FROM Shares)"
        Dim sqlCmd As Command = Environment.NewCommand(strSql)
        Environment.ExecuteNonQuery(sqlCmd)
    End Sub


    Public Shared Sub Save(ByVal item As Entities.SharedItem)
        Using t As New TransactionScope(TransactionScope.TransactionMode.NullTransaction)
            Save(item, t)
            t.Complete()
        End Using
    End Sub

    Public Shared Sub Save(ByVal item As Entities.SharedItem, ByVal transactionScope As TransactionScope)
        Dim clsCmd As New SaveCommand(Of Integer)("SharedItems", "Id", item.Id, 0)
        clsCmd.AddParam("Added", item.Added, False)
        clsCmd.AddParam("Directory", item.Directory, False)
        clsCmd.AddParam("LastChanged", item.LastChanged, False)
        clsCmd.AddParam("Name", item.Name, False)
        clsCmd.AddParam("ShareId", item.ShareId, False)
        clsCmd.AddParam("UniqueId", item.UniqueId, False)

        clsCmd.AddParam("TotalFiles", item.TotalFiles, False)
        clsCmd.AddParam("TotalSize", item.TotalSize, False)
        clsCmd.AddParam("Comment", item.Comment, True)
        clsCmd.AddParam("GroupMode", item.GroupMode, False)
        clsCmd.AddParam("ItemType", item.ItemType, False)
        clsCmd.AddParam("HasMediaInfo", item.HasMediaInfo, False)
        clsCmd.AddParam("RelativeDirectory", item.RelativeDirectory, False)

        item.Id = clsCmd.Save(transactionScope)
    End Sub
End Class
